from dbutils.pooled_db import PooledDB
from typing import Optional
import pymysql,os,logging
from dotenv import load_dotenv

load_dotenv()

driver = os.getenv("DB_DRIVER","pymysql")
host=os.getenv("DB_HOST","127.0.0.1")
port=os.getenv("DB_PORT",3306)
user=os.getenv("DB_USERNAME","root")
passwd=os.getenv("DB_PASSWORD","root")
database=os.getenv("DB_DATABASE","hypercmf_py")
charset=os.getenv("DB_CHARSET","utf8mb4")

#连接池
POOL = PooledDB(creator=pymysql,
                maxconnections=10,#  最大连接数
                mincached=5,# 最小连接数
                maxcached=10,# 最大空闲连接数
                maxshared=10,# 最大共享连接数
                blocking=True,# 超时等待
                ping=7,# ping MySQL服务端，检查是否服务可用
                host=host,
                port=int(port),
                user=user,
                passwd=passwd,
                db=database,
                charset=charset)

#执行原生sql语句的db组件
def db(sql,  params:Optional[list]=[]):
    logging.info("params:" % params)
    logging.info("sql: %s" % sql)
    conn = POOL.connection()
    cursor = conn.cursor()
    try:
        cursor.execute(sql, params)
        result = cursor.fetchall()
        return result
    except Exception as e:
        logging.error("sqlException: %s" % e)
    finally:
        cursor.close()
        conn.close() #不是关闭连接，只是释放链接到连接池

# tortoise-orm配置
TORTDISE_ORM = {
    'connections': {
        'default':{
            #'engine':'tortoise.backends.asyncpg'，PostqresOL
            'engine':'tortoise.backends.mysql',  # MySQL or Mariadbcredentials':
            'default_schema': 'for_',
            'credentials': {
                'host':host,
                'port':port,
                'user':user,
                'password':passwd,
                'database':database,
                'minsize':1,
                'maxsize':5,
                'charset':charset,
                'echo': True,
            }
        }
    },
    'apps':{
        'models':{
            'models':['models.article_model','models.user_model'],
            'default_connection':'default',
        }
    },
    'use_tz':False,
    'timezone':'Asia/shanghai'
}